package com.example.demo.mapper;

import com.example.demo.entity.ScRecord;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Mapper
@Repository
public interface ScRecordMapper extends BaseMapper<ScRecord> {

    @Select("SELECT * FROM sc_record WHERE id IN (SELECT max(id) FROM sc_record " +
            "WHERE attendance_type = #{department} GROUP BY `name`);")
    List<ScRecord> getNameList1(String department);

    @Select("SELECT * FROM sc_record WHERE id IN (SELECT max(id) FROM sc_record " +
            "WHERE date >= #{date1} AND date <= #{date2} " +
            "AND attendance_type = #{department} " +
            "GROUP BY `name`);")
    List<ScRecord> getNameList2(String date1, String date2, String department);

    @Select("SELECT * FROM sc_record WHERE `name` = #{name}")
    List<ScRecord> getByName(String name);

    @Select("SELECT `name`, attendance_type, SUM(IF(lack=0,1,0)) AS days, SUM(IF(lack=1,1,0)) AS lack, SUM(work_time) AS work_time " +
            "FROM sc_record WHERE date >= #{date1} AND date <= #{date2} " +
            "AND attendance_type like #{department} " +
            "GROUP BY `name`, attendance_type ORDER BY work_time DESC;")
    List<Map<Object, Object>> getSum(String date1, String date2, String department);

    @Select("SELECT `name`, SUM(work_time) AS work_time FROM sc_record " +
            "WHERE lack = 0 AND date >= #{first} AND date <= #{last} AND attendance_type = #{department} " +
            "GROUP BY `name` ORDER BY work_time DESC LIMIT 10;")
    List<HashMap<String, BigDecimal>> getRank(String first, String last, String department);
}




